﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Spring.Data;
using Spring.Data.Generic;
using Spring.Data.Common;
using Spring.Data.Objects.Generic;
using Spring.Transaction;
using Spring.Transaction.Support;

namespace Ordering
{
    class MyDAO2
    {
        #region Property
        private static AdoTemplate template = AppConfigurer.AdoTemplate;
        #endregion

        //public static string GetTableName(string type)
        //{
        //    switch (type)
        //    {
        //        case "Shop":
        //            return "Shop";
        //    }
        //    return null;
        //}

        //public static IList<T> GetObject<T>(T obj)
        //{
        //    IList<T> lst = new List<T>();
        //    string strSQL = "select * from " + GetTableName(obj.GetType().ToString());
        //    return template.QueryWithRowCallbackDelegate(CommandType.Text, strSQL, delegate(IDataReader dr)
        //    {
        //        T t = new T();
        //    };
        //    return lst;
        //}

        #region ShopDao

        internal static Shop GetShop(string shopId)
        {
            IList<Shop> lst = new List<Shop>();
            string strSQL = "select * from [Shop] where Shop_Id = @shopId";
            IDbParameters para = template.CreateDbParameters();
            para.Add("shopId", DbType.String).Value = shopId;
            template.QueryWithRowCallbackDelegate(CommandType.Text, strSQL, delegate(IDataReader dr)
            {
                lst.Add(new Shop
                {
                    Shop_Id = dr.IsDBNull(0) ? null : dr.GetString(0),
                    Shop_Name = dr.IsDBNull(1) ? null : dr.GetString(1),
                    Shop_Address = dr.IsDBNull(2) ? null : dr.GetString(2),
                    Shop_Phone_No = dr.IsDBNull(3) ? null : dr.GetString(3),
                    Shop_Description = dr.IsDBNull(5) ? null : dr.GetString(5)
                });
            }, para);
            return lst.Count > 0 ? lst[0] : null;
        }

        internal static IList<Shop> GetShops()
        {
            IList<Shop> lst = new List<Shop>();
            string strSQL = "select * from [Shop]";
            template.QueryWithRowCallbackDelegate(CommandType.Text, strSQL, delegate(IDataReader dr)
            {
                lst.Add(new Shop
                {
                    Shop_Id = dr.IsDBNull(0) ? null : dr.GetString(0),
                    Shop_Name = dr.IsDBNull(1) ? null : dr.GetString(1),
                    Shop_Address = dr.IsDBNull(2) ? null : dr.GetString(2),
                    Shop_Phone_No = dr.IsDBNull(3) ? null : dr.GetString(3),
                    Shop_Description = dr.IsDBNull(5) ? null : dr.GetString(5)
                });
            });
            return lst;
        }

        internal static void AddShop(Shop shop)
        {
            //Check input parameter
            if (shop == null)
                throw new Exception("MyDao2.AddShop: Invalid parameter");

            //Get shopId
            string strSearch = "SELECT isnull(max(Shop_Id),'S00000000') FROM Shop ";
            string strMaxNo = template.ExecuteScalar(CommandType.Text, strSearch).ToString();
            int nNum = Int32.Parse(strMaxNo.Substring(1)) + 1;
            shop.Shop_Id = "S" + nNum.ToString("00000000");

            string strSQL = "insert into [Shop](Shop_Id, Shop_Name, Shop_Phone_No, Shop_Address, Shop_Description) " +
                " values(@ShopId, @ShopName, @ShopPhoneNo, @ShopAddress, @ShopDescription)";

            IDbParameters para = template.CreateDbParameters();
            para.Add("ShopId", DbType.String).Value = shop.Shop_Id;
            para.Add("ShopName", DbType.String).Value = shop.Shop_Name;
            para.Add("ShopPhoneNo", DbType.String).Value = shop.Shop_Phone_No;
            para.Add("ShopAddress", DbType.String).Value = shop.Shop_Address;
            para.Add("ShopDescription", DbType.String).Value = shop.Shop_Description;

            template.ExecuteNonQuery(CommandType.Text, strSQL, para);
        }

        internal static void UpdateShop(Shop shop)
        {
            string strSQL = "update [Shop] set Shop_Name=@shopName, Shop_Phone_No=@shopPhoneNo, "
                + "Shop_Address=@shopAddress, Shop_Description=@shopDescription where Shop_Id=@shopId";

            IDbParameters para = template.CreateDbParameters();
            para.Add("shopId", DbType.String).Value = shop.Shop_Id;
            para.Add("shopName", DbType.String).Value = shop.Shop_Name;
            para.Add("shopPhoneNo", DbType.String).Value = shop.Shop_Phone_No;
            para.Add("shopAddress", DbType.String).Value = shop.Shop_Address;
            para.Add("shopDescription", DbType.String).Value = shop.Shop_Description;

            template.ExecuteNonQuery(CommandType.Text, strSQL, para);
        }

        internal static void RemoveShop(string shopId)
        {
            string strSQL = "delete from [Shop] where Shop_Id = @shopId";
            IDbParameters para = template.CreateDbParameters();
            para.Add("shopId", DbType.String).Value = shopId;
            template.ExecuteNonQuery(CommandType.Text, strSQL, para);
        }

        #endregion 

        #region DishDao

        internal static Dish GetDish(string dishId)
        {
            IList<Dish> lst = new List<Dish>();
            string strSQL = "select * from [Dish] where Dish_Id = @dishId";
            IDbParameters para = template.CreateDbParameters();
            para.Add("dishId", DbType.String).Value = dishId;
            template.QueryWithRowCallbackDelegate(CommandType.Text, strSQL, delegate(IDataReader dr)
            {
                lst.Add(new Dish
                {                    
                    //Shop_Name = dr.IsDBNull(1) ? null : dr.GetString(1),
                    Dish_Id = dr.IsDBNull(0) ? null : dr.GetString(0),
                    Shop_Id = dr.IsDBNull(1) ? null : dr.GetString(1),
                    Dish_Name = dr.IsDBNull(2) ? null : dr.GetString(2),
                    Dish_Price = dr.IsDBNull(3) ? 0 : dr.GetDouble(3),
                    Dish_Desc = dr.IsDBNull(4) ? null : dr.GetString(4)
                });
            }, para);

            return lst.Count > 0 ? lst[0] : null;
        }

        internal static IList<Dish> GetDishes(string shopId)
        {
            IList<Dish> lst = new List<Dish>();
            IDbParameters para = template.CreateDbParameters();
            string strSQL = "select * from [Dish] dish left join [Shop] shop on dish.Shop_Id = shop.Shop_Id ";
            if (shopId != null)
            {             
                strSQL += "where dish.Shop_Id = @shop_Id";
                para.Add("shop_Id", DbType.String).Value = shopId;
            }                       
            template.QueryWithRowCallbackDelegate(CommandType.Text, strSQL, delegate(IDataReader dr)
            {
                lst.Add(new Dish{
                    Dish_Id = dr.GetString(0),
                    Shop_Id = dr.GetString(1),
                    Dish_Name = dr.GetString(2),
                    Dish_Price = Convert.ToDouble(dr.GetString(3)),
                    Dish_Desc = dr.GetString(4)
                });
            }, para);
            return lst;                
        }

        internal static IList<IDictionary<string, string>> GetDishesForMenu()
        {
            IList<IDictionary<string, string>> lst = new List<IDictionary<string, string>>();
            IDictionary<string, string> dict = new Dictionary<string, string>();
            string strSQL = "select dish.Shop_Id as shopId, shop.Shop_Name as shopName, "
                + "Dish_Id as dishId, Dish_Name as dishName, Dish_Price as dishPrice, Dish_Desc as dishDesc "
                + "from [Dish] dish left join [Shop] shop on dish.Shop_Id = shop.Shop_Id ";
            //IDbParameters para = template.CreateDbParameters();
            template.QueryWithRowCallbackDelegate(CommandType.Text, strSQL, delegate(IDataReader dr)
            {
                lst.Add(new Dictionary<string, string>{
                    {"ShopId", dr.IsDBNull(0) ? null : dr.GetString(0)},
                    {"ShopName", dr.IsDBNull(1) ? null : dr.GetString(1)},
                    {"DishId", dr.IsDBNull(2) ? null : dr.GetString(2)},
                    {"DishName", dr.IsDBNull(3) ? null : dr.GetString(3)},
                    {"DishPrice", dr.IsDBNull(4) ? null : dr.GetDouble(4).ToString()},
                    {"DishDesc", dr.IsDBNull(5) ? null : dr.GetString(5)}
                });
            });
            return lst;
        }

        internal static IList<IDictionary<string, string>> GetDishesForMenuByShopId(string shopId)
        {
            IList<IDictionary<string, string>> lst = new List<IDictionary<string, string>>();
            IDictionary<string, string> dict = new Dictionary<string, string>();            
            string strSQL = "select shop.Shop_Id as shopId, shop.Shop_Name as shopName, "
                + "Dish_Id as dishId, Dish_Name as dishName, Dish_Price as dishPrice, Dish_Desc as dishDesc "
                + "from [Dish] dish left join [Shop] shop on dish.Shop_Id = shop.Shop_Id "
                + "where dish.Shop_Id = @shop_Id";
            IDbParameters para = template.CreateDbParameters();
            para.Add("shop_Id", DbType.String).Value = shopId;
            template.QueryWithRowCallbackDelegate(CommandType.Text, strSQL, delegate(IDataReader dr)
            {
                lst.Add(new Dictionary<string, string>{
                    {"ShopId", dr.IsDBNull(0) ? null : dr.GetString(0)},
                    {"ShopName", dr.IsDBNull(1) ? null : dr.GetString(1)},
                    {"DishId", dr.IsDBNull(2) ? null : dr.GetString(2)},
                    {"DishName", dr.IsDBNull(3) ? null : dr.GetString(3)},
                    {"DishPrice", dr.IsDBNull(4) ? null : dr.GetDouble(4).ToString()},
                    {"DishDesc", dr.IsDBNull(5) ? null : dr.GetString(5)}
                });
            }, para);
            return lst;
        }

        internal static void AddDish(Dish dish)
        {
            //Check input parameter
            if (dish == null)
                throw new Exception("MyDAO2.AddDish: Invalid parameter");

            //Get dishId
            string strSearch = "SELECT isnull(max(Dish_Id),'D00000000') FROM [Dish] ";
            string strMaxNo = template.ExecuteScalar(CommandType.Text, strSearch).ToString();
            int nNum = Int32.Parse(strMaxNo.Substring(1)) + 1;
            dish.Dish_Id = "D" + nNum.ToString("00000000");

            string strSQL = "insert into [Dish] values(@dishId, @shopId, @dishName, @dishPrice, @dishDesc)";

            IDbParameters para = template.CreateDbParameters();
            para.Add("shopId", DbType.String).Value = dish.Shop_Id;
            para.Add("dishId", DbType.String).Value = dish.Dish_Id;
            para.Add("dishName", DbType.String).Value = dish.Dish_Name;
            para.Add("dishPrice", DbType.String).Value = dish.Dish_Price;
            para.Add("dishDesc", DbType.String).Value = dish.Dish_Desc;

            template.ExecuteNonQuery(CommandType.Text, strSQL, para);
        }

        internal static void UpdateDish(Dish dish)
        {
            string strSQL = "update [Dish] set Dish_Name = @dishName, Dish_Price = @dishPrice, Dish_Desc = @dishDesc "
                + "where Dish_Id = @dishId";

            IDbParameters para = template.CreateDbParameters();
            para.Add("dishId", DbType.String).Value = dish.Dish_Id;
            para.Add("dishName", DbType.String).Value = dish.Dish_Name;
            para.Add("dishPrice", DbType.Double).Value = dish.Dish_Price;
            para.Add("dishDesc", DbType.String).Value = dish.Dish_Desc;

            template.ExecuteNonQuery(CommandType.Text, strSQL, para);
        }

        internal static void RemoveDish(string dishId)
        {
            string strSQL = "delete from [Dish] where Dish_Id = @dishId";
            IDbParameters para = template.CreateDbParameters();
            para.Add("dishId", DbType.String).Value = dishId;
            template.ExecuteNonQuery(CommandType.Text, strSQL, para);
        }

        #endregion

        #region OrderDao

        internal static IList<IDictionary<string, string>> ListOrderByGroupType(GroupType type, string staffId)
        {
            // The sql for query
            string baseSql = "select ord.Order_Id as orderId, ord.Staff_Id as StaffId, st.Staff_Name as StaffName, " +
                "st.Staff_Dept as StaffDept, di.Dish_Name as DishName, ord.Order_Datetime as OrderDatetime from [Orders] ord " +
                "left join [Staff] st on ord.Staff_Id = st.Staff_Id " +
                "left join [Dish] di on ord.Dish_Id = di.Dish_Id " +
                "left join [Shop] sh on di.Shop_Id = sh.Shop_Id ";

            string whereSql = " where ord.Order_DateTime > @orderDatetime ";
            switch (type)
            {
                case GroupType.MyDepartment:
                    break;
                case GroupType.MyTeam:
                    whereSql += " and ord.Staff_Id in ( select Staff_Id from [Teammate] where Team_Id in ( select Team_Id from [Teammate] where Staff_Id = @staffId )) ";
                    break;
                case GroupType.MyFriend:
                    whereSql += " and ord.Staff_Id in ( select Friend_Id from [Friend] where Staff_Id = @staffId ) ";
                    break;
                default:
                    whereSql += null;
                    break;
            }
            string orderSql = " order by ord.Staff_Id, ord.Order_DateTime ";
            string strSQL = baseSql + whereSql + orderSql;

            IDbParameters para = template.CreateDbParameters();
            para.Add("staffId", DbType.String).Value = staffId;
            para.Add("orderDatetime", DbType.DateTime).Value = GetMonday(); 

            IList<IDictionary<string, string>> lst = new List<IDictionary<string, string>>();
            template.QueryWithRowCallbackDelegate(CommandType.Text, strSQL, delegate(IDataReader dr)
            {
                IDictionary<string, string> dict = new Dictionary<string, string>();
                dict.Add("OrderId", dr.GetString(0));
                dict.Add("StaffId", dr.GetString(1));
                dict.Add("StaffName", dr.GetString(2));
                dict.Add("StaffDept", dr.GetString(3));
                dict.Add("DishName", dr.GetString(4));
                dict.Add("OrderDatetime", dr.GetDateTime(5).ToString());
                lst.Add(dict);
            }, para);

            return lst;
        }

        internal static IList<IDictionary<string, string>> ListOrdersViaShop()
        {
            IList<IDictionary<string, string>> lst = new List<IDictionary<string, string>>();

            string strSQL = 
                " select *                                               " +
                " from(                                                  " +
                "	 select di.Shop_Id as ShopId, count(*) as CntDish,   " +
                "	 sum(di.Dish_Price) as TotalCount from [Orders] ord  " +
                "	 left join [Staff] st on ord.Staff_Id = st.Staff_Id  " +
                "	 left join [Dish] di on ord.Dish_Id = di.Dish_Id     " +
                "	 group by di.Shop_Id) tmp                            " +
                "	left join [Shop] sh on tmp.shopId = sh.Shop_Id   	 ";

            template.QueryWithRowCallbackDelegate(CommandType.Text, strSQL, delegate(IDataReader dr)
            {
                IDictionary<string, string> dict = new Dictionary<string, string>();
                dict.Add("CntDish", dr.GetInt32(1).ToString());
                dict.Add("TotalCount", dr.GetDouble(2).ToString());
                dict.Add("ShopId", dr.GetString(3));
                dict.Add("ShopName", dr.GetString(4));
                dict.Add("ShopAddress", dr.GetString(5));
                dict.Add("ShopPhoneNo", dr.GetString(6));
                dict.Add("ShopDesc", dr.IsDBNull(8) ? null : dr.GetString(8));
                lst.Add(dict);
            });

            return lst;
        }

        internal static IList<IDictionary<string, string>> ListOrdersViaDish(string shopId)
        {
            IList<IDictionary<string, string>> lst = new List<IDictionary<string, string>>();

            string strSQL =
                " select * " +
                " from( " +
                "	 select di.Dish_Id as DishId, count(*) as CntDish,  " +
                "	 sum(di.Dish_Price) as TotalCount from [Orders] ord " +
                "	 left join [Staff] st on ord.Staff_Id = st.Staff_Id " +
                "	 left join [Dish] di on ord.Dish_Id = di.Dish_Id  " +
                "	 where di.Shop_Id = @shopId " +
                "	 group by di.Dish_Id) tmp " +
                "	left join [Dish] di on tmp.DishId = di.Dish_Id  ";

            IDbParameters para = template.CreateDbParameters();
            para.Add("shopId", DbType.String).Value = shopId;

            template.QueryWithRowCallbackDelegate(CommandType.Text, strSQL, delegate(IDataReader dr)
            {
                IDictionary<string, string> dict = new Dictionary<string, string>();
                dict.Add("DishId", dr.GetString(0));
                dict.Add("CntDish", dr.GetInt32(1).ToString());
                dict.Add("TotalCount", dr.GetDouble(2).ToString());
                dict.Add("ShopId", dr.GetString(4));
                dict.Add("DishName", dr.GetString(5));
                dict.Add("DishPrice", dr.GetDouble(6).ToString());
                dict.Add("DishDesc", dr.IsDBNull(7) ? null : dr.GetString(7));
                lst.Add(dict);
            }, para);

            return lst;
        }

        internal static IList<IDictionary<string, string>> ListOrdersViaStaff(string dishId)
        {
            IList<IDictionary<string, string>> lst = new List<IDictionary<string, string>>();
            
            string strSQL =
                " select * " +
                " from [Orders] ord " +
                " left join [Staff] st on ord.Staff_Id = st.Staff_Id " +
                " where ord.Dish_Id = '@dishId' ";

            IDbParameters para = template.CreateDbParameters();
            para.Add("dishId", DbType.String).Value = dishId;

            template.QueryWithRowCallbackDelegate(CommandType.Text, strSQL, delegate(IDataReader dr)
            {
                IDictionary<string, string> dict = new Dictionary<string, string>();
                dict.Add("OrderId", dr.GetString(0));
                dict.Add("StaffId", dr.GetString(1));
                dict.Add("DishId", dr.GetString(2));
                dict.Add("OrderTime", dr.GetDateTime(3).ToString());
                dict.Add("OrderStatus", dr.GetString(5));
                dict.Add("StaffName", dr.GetString(7));
                dict.Add("StaffDept", dr.GetString(8));
                dict.Add("DishName", dr.GetString(11));
                dict.Add("DishPrice", dr.GetDouble(12).ToString());
                lst.Add(dict);
            }, para);
            return lst;
        }
        internal static void AddOrder(Order order)
        {
            //Check input parameter
            if (order == null)
                throw new Exception("MYDAO2.AddOrder: Invalia parameter");

            //Get OrderId

            string strSearch = "select isnull(max(Order_Id), 'O00000000') from [Orders]";
            string strMaxNo = template.ExecuteScalar(CommandType.Text, strSearch).ToString();
            int nNum = Int32.Parse(strMaxNo.Substring(1)) + 1;
            order.Order_Id = "O" + nNum.ToString("00000000");

            string strSQL = "insert into [Orders] values(@orderId, @staffId, @dishId, @orderDatetime, @remark, '1')";

            IDbParameters para = template.CreateDbParameters();
            para.Add("orderId", DbType.String).Value = order.Order_Id;
            para.Add("staffId", DbType.String).Value = order.Staff_Id;
            para.Add("dishId", DbType.String).Value = order.Dish_Id;
            para.Add("orderDatetime", DbType.DateTime).Value = order.Order_Datetime;
            para.Add("remark", DbType.String).Value = order.Remark;

            template.ExecuteNonQuery(CommandType.Text, strSQL, para);
        }

        #endregion

        #region FriendDao

        internal static int AddFriend(string staffId, string friendId)
        {
            string strSQL = "insert into Friend values(@staffId, @friendId)";
            IDbParameters para = template.CreateDbParameters();
            para.Add("staffId", DbType.String).Value = staffId;
            para.Add("friendId", DbType.String).Value = friendId;
            return template.ExecuteNonQuery(CommandType.Text, strSQL, para);
        }

        internal static int removeFriend(string staffId, string friendId)
        {
            string strSQL = "delete from Friend where Staff_Id = @staffId and Friend_Id = @friendId";
            IDbParameters para = template.CreateDbParameters();
            para.Add("staffId", DbType.String).Value = staffId;
            para.Add("friendId", DbType.String).Value = friendId;
            return template.ExecuteNonQuery(CommandType.Text, strSQL, para);
        }

        internal static bool isFriend(string staffId, string friendId)
        {
            string strSQL = "select count(*) from Friend where Staff_Id = @staffId and Friend_Id = @friendId";
            IDbParameters para = template.CreateDbParameters();
            para.Add("staffId", DbType.String).Value = staffId;
            para.Add("friendId", DbType.String).Value = friendId;
            int cnt = (int)template.ExecuteScalar(CommandType.Text, strSQL, para);
            return cnt == 0 ? false : true;
        }

        #endregion

        #region Helper

        public string IDataReaderHelper(IDataReader dr, int index)
        {
            return dr.IsDBNull(index) ? dr.GetString(index) : null;
        }

        private static DateTime GetMonday()
        {
            DateTime dt = DateTime.Now;
            while (dt.DayOfWeek != DayOfWeek.Monday)
                dt = dt.AddDays(-1);
            return dt = new DateTime(dt.Year, dt.Month, dt.Day);
        }

        #endregion
    }
}
